-- check what locks are in place and use the locks to determine if blocking is occuring
-- what is the resource_associated_entity_id, request_mode,
-- request_type and request_session_id from the row that has request_status = 'Wait'?
SELECT 
    request_session_id AS session_id, 
    resource_type,  
    resource_database_id, 
    (CASE resource_type
      WHEN 'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id)
      WHEN 'DATABASE' THEN ' '
      ELSE (SELECT OBJECT_NAME(object_id) FROM sys.partitions 
            WHERE hobt_id = resource_associated_entity_id)
    END) AS 'object_name', 
    resource_description, 
    resource_associated_entity_id, 
    request_mode, 
    request_status,
    request_session_id,
    request_owner_type,
    request_owner_id
FROM sys.dm_tran_locks
WHERE resource_type NOT IN ('DATABASE', 'METADATA');

-- investigating blocking using sys.dm_os_waiting_tasks
SELECT
	DB_NAME(blocked.database_id) AS database_name,
	blocked.wait_resource,
	blocking.session_id AS blocking_session_id,
    blocked.session_id AS blocked_session_id,
    waitstats.wait_type AS blocking_resource,
	waitstats.wait_duration_ms,
    waitstats.resource_description,
    blocking_cache.text AS blocking_text,
	blocked_cache.text AS blocked_text
FROM sys.dm_exec_connections AS blocking
INNER JOIN sys.dm_exec_requests blocked ON blocking.session_id = blocked.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_cache
CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle) blocking_cache
INNER JOIN sys.dm_os_waiting_tasks waitstats ON waitstats.session_id = blocked.session_id;

-- looking at the blocking hierarchy
WITH BlockingHierarchy
	(head_wait_resource, session_id, blocking_session_id, tree_level, request_id, transaction_id, 
	status, sql_handle, plan_handle, statement_start_offset, statement_end_offset, wait_type, wait_time, wait_resource, 
	program_name, seconds_active_idle, open_transaction_count, transaction_isolation_level) 
AS 
(
	SELECT 
		(SELECT MIN(wait_resource) FROM sys.dm_exec_requests
			WHERE blocking_session_id = s.session_id) AS head_wait_resource, 
		s.session_id, 
		CONVERT(smallint, NULL) AS blocking_session_id, 
		CONVERT(int, 0) AS tree_level, 
		r.request_id, 
		COALESCE(r.transaction_id, st.transaction_id) AS transaction_id, 
		ISNULL(r.status, 'idle') AS status, 
		r.sql_handle, 
		r.plan_handle, 
		r.statement_start_offset, 
		r.statement_end_offset, 
		r.wait_type, 
		r.wait_time, 
		r.wait_resource, 
		s.program_name,
		CASE WHEN r.request_id IS NULL THEN DATEDIFF(ss, s.last_request_end_time, GETDATE())
			ELSE DATEDIFF(ss, r.start_time, GETDATE()) END AS seconds_active_idle,
		CONVERT(int, p.open_tran) AS open_tran,
		COALESCE(r.transaction_isolation_level, s.transaction_isolation_level) AS transaction_isolation_level
	FROM sys.dm_exec_sessions s
		INNER JOIN sys.sysprocesses p ON s.session_id = p.spid
		LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
		LEFT JOIN sys.dm_tran_session_transactions st ON s.session_id = st.session_id
	WHERE s.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests) 
		AND ISNULL(r.blocking_session_id, 0) = 0
	UNION ALL
	SELECT
		b.head_wait_resource, 
		r.session_id, 
		r.blocking_session_id, 
		tree_level + 1, 
		r.request_id, 
		r.transaction_id, 
		r.status, 
		r.sql_handle, 
		r.plan_handle, 
		r.statement_start_offset, 
		r.statement_end_offset, 
		r.wait_type, 
		r.wait_time, 
		r.wait_resource, 
		NULL,
		NULL,
		r.open_transaction_count,
		r.transaction_isolation_level
	FROM sys.dm_exec_requests r
		INNER JOIN BlockingHierarchy b ON r.blocking_session_id = b.session_id
)
SELECT
	bh.head_wait_resource,
	bh.session_id, 
	bh.request_id, 
	bh.blocking_session_id, 
	bh.program_name, 
	bh.tree_level,
	CASE qt.encrypted WHEN 1 THEN N'encrypted query text' 
	ELSE
		SUBSTRING(qt.text, (bh.statement_start_offset/2) + 1,   
			((CASE bh.statement_end_offset  
			  WHEN -1 THEN DATALENGTH(qt.text)  
			 ELSE bh.statement_end_offset  
			 END - bh.statement_start_offset)/2) + 1)
	END AS query_text,
	--CASE WHEN LEN(qt.query_text) < 2048 THEN qt.query_text
	--	ELSE LEFT(qt.query_text, 2048) + N'...' END AS query_text,
	master.dbo.fn_varbintohexstr(bh.sql_handle) AS sql_handle, 
	master.dbo.fn_varbintohexstr(bh.plan_handle) AS plan_handle, 
	bh.statement_start_offset, 
	bh.statement_end_offset, 
	bh.status AS session_or_request_status, 
	bh.wait_type, 
	bh.wait_time, 
	bh.wait_resource, 
	bh.transaction_id, 
	bh.transaction_isolation_level,
	bh.open_transaction_count,
	bh.seconds_active_idle,
	at.name AS transaction_name, 
	at.transaction_begin_time, 
	at.transaction_type, 
	at.transaction_state, 
	at.dtc_state, 
	at.dtc_isolation_level,
	st.enlist_count, 
	st.is_user_transaction, 
	st.is_local, 
	st.is_enlisted, 
	st.is_bound
FROM BlockingHierarchy bh
	LEFT JOIN sys.dm_tran_session_transactions st ON st.transaction_id = bh.transaction_id and st.session_id = bh.session_id
	LEFT JOIN sys.dm_tran_active_transactions at ON at.transaction_id = bh.transaction_id
	OUTER APPLY sys.dm_exec_sql_text(bh.sql_handle) qt;